﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;


using Dapper;
using Dapper.Contrib;
using Dapper.Contrib.Extensions;
using TmsDapper;
using HYweixinOnline.Entities;
using HYweixinOnline.Common;

using System.Data;

namespace HYweixinOnline.Admin
{
   public  class SysPagePowerBaseDAO
    {
        /// <summary>
        /// 取所有的对象
        /// </summary>
        /// <returns></returns>
        public IList<TbSysPagePower> GetAll()
        {

            IList<TbSysPagePower> tmplist;
            using (var connection = DataBase.GetOpenConnection())
            {

                tmplist = connection.Query<TbSysPagePower>("select * from tb_Sys_PagePower").ToList<TbSysPagePower>();


            }
            return tmplist;
        }


        /// <summary>
        /// 通过ID查询对象
        /// </summary>
        /// <param name="id">对象的ID</param>
        /// <returns></returns>
        public TbSysPagePower GetByID(int id)
        {
            TbSysPagePower curItem = null;
            using (var connection = DataBase.GetOpenConnection())
            {

                curItem = connection.Get<TbSysPagePower>(id);


            }

            return curItem;

        }




        /// <summary>
        /// 条件查询分页
        /// </summary>
        /// <param name="whereClause">条件</param>
        /// <param name="startRowIndex">起始值</param>
        /// <param name="maximumRows">每页的最大值</param>
        /// <returns></returns>
        [System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select, false)]
        public IList<TbSysPagePower> GetByPage(string whereClause, int startRowIndex, int maximumRows)
        {
            //(string whereClause, int start, int limit, string sort, string dir, out int count)
            int TotalCount = 0;
            IList<TbSysPagePower> tempList = GetByPage(whereClause, (int)startRowIndex / maximumRows, maximumRows, "ID", "DESC", out TotalCount);
            return tempList;
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="whereClause">查询条件</param>
        /// <param name="start">起始位置</param>
        /// <param name="limit">最大条数</param>
        /// <param name="sort">排序列</param>
        /// <param name="dir">排序方向</param>
        /// <param name="count">取得记录数</param>
        /// <returns></returns>
        public IList<TbSysPagePower> GetByPage(string whereClause, int start, int limit, string sort, string dir, out int count)
        {

            IList<TbSysPagePower> tmplist;
            string Sqlstring = SQLHelper.GenPageSQL("tb_Sys_PagePower", whereClause, start, limit, sort, dir);
            using (var connection = DataBase.GetOpenConnection())
            {

                tmplist = connection.Query<TbSysPagePower>(Sqlstring).ToList<TbSysPagePower>();

                count = GetCount(whereClause);
            }

            return tmplist;
        }

        /// <summary>
        ///兼容查询
        /// </summary>
        /// <param name="whereClause">查询条件</param>
        /// <param name="start">起始位置</param>
        /// <param name="limit">最大条数</param>
        /// <param name="sort">排序列</param>
        /// <param name="dir">排序方向("DESC","ASC")</param>
        /// <param name="count">取得记录数</param>
        /// <returns>TList<???></returns>

        public IList<TbSysPagePower> GetByPageDataBase(int start, int limit, string sort, string whereClause, out int count)
        {
            IList<TbSysPagePower> tmplist;
            string Sqlstring = SQLHelper.GenPageDataBaseSQL("tb_Sys_PagePower", whereClause, start, limit, sort);
            using (var connection = DataBase.GetOpenConnection())
            {

                tmplist = connection.Query<TbSysPagePower>(Sqlstring).ToList<TbSysPagePower>();

                count = GetCount(whereClause);
            }

            return tmplist;
        }






        /// <summary>
        /// 获取分页查询的总记录条数
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public int GetCount(string where = "")
        {
            int count = 0;
            using (var connection = DataBase.GetOpenConnection())
            {
                string sqlStr = "select count(1) from tb_Sys_PagePower";
                if (!string.IsNullOrEmpty(where))
                {
                    sqlStr += " where " + where;
                }
                List<int> tmpList = connection.Query<int>(sqlStr).ToList<int>();
                count = tmpList[0];


            }

            return count;


        }


        /// <summary>
        /// 通过对象更新记录
        /// </summary>
        /// <param name="item">需要更新的对象</param>
        /// <returns></returns>
        public bool Update(TbSysPagePower item)
        {
            bool res = false;
            using (var connection = DataBase.GetOpenConnection())
            {

                res = connection.Update<TbSysPagePower>(item);

            }
            return res;
        }




        /// <summary>
        /// 通过ID删除记录
        /// </summary>
        /// <param name="id">需要删除的对象的ID</param>
        /// <returns></returns>
        public bool Del(int id)
        {
            bool res = false;
            TbSysPagePower item = new TbSysPagePower();
            using (var connection = DataBase.GetOpenConnection())
            {
                item = GetByID(id);
                res = connection.Delete<TbSysPagePower>(item);
            }
            return res;
        }

        /// <summary>
        /// 通过对象删除记录
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public bool Del(TbSysPagePower item)
        {

            bool res = false;
            using (var connection = DataBase.GetOpenConnection())
            {
                res = connection.Delete<TbSysPagePower>(item);
            }
            return res;
        }

        /// <summary>
        /// 通过对象删除记录
        /// </summary>
        /// <param name="items">需删除的IList对象</param>
        /// <returns></returns>
        public bool Del(IList<TbSysPagePower> items)
        {
            bool res = false;
            using (var connection = DataBase.GetOpenConnection())
            {
                foreach (var item in items)
                {
                    connection.Delete<TbSysPagePower>(item);
                }
                res = true;
            }
            return res;

        }



        /// <summary>
        /// 插入一个对象
        /// </summary>
        /// <param name="item">需要删除的对象</param>
        /// <returns></returns>
        public long Insert(TbSysPagePower item)
        {
            long id = 0;
            using (var connection = DataBase.GetOpenConnection())
            {
                id = connection.Insert<TbSysPagePower>(item);
            }
            return id;
        }


        /// <summary>
        /// 插入一个IList对象
        /// </summary>
        /// <param name="items">需要删除的对象集合</param>
        /// <returns></returns>
        public bool Insert(IList<TbSysPagePower> items)
        {
            bool res = false;
            using (var connection = DataBase.GetOpenConnection())
            {
                foreach (var item in items)
                {
                    connection.Insert<TbSysPagePower>(item);
                }
                res = true;
            }
            return res;
        }



        /*
       *get the Object through IParentId
       */
        public IList<TbSysPagePower> findByIParentId(object _IParentId)
        {

            return findByProperty("IParentId", _IParentId);
        }

        public IList<TbSysPagePower> findByProperty(String propertyName, Object value)
        {
            IList<TbSysPagePower> tmpList;
            try
            {

                string whereC = "";
                string FirC = propertyName.Substring(0, 1);
                if (FirC != "I" || FirC != "F")
                {

                    value = "'" + value.ToString() + "'";
                }

                if (!String.IsNullOrEmpty(propertyName) && propertyName.ToUpper() != "ID")
                {
                    //whereC = propertyName.Insert(1, "_") + "= " + value.ToString();
                    whereC = propertyName + "= " + value.ToString();
                }
                else
                {
                    whereC = propertyName + "= " + value.ToString();
                }


                tmpList = GetByPage(whereC, 0, int.MaxValue);
                return tmpList;
            }
            catch (Exception re)
            {
                throw re;
            }
        }//end for findByProperty


        //	CREATE Function [dbo].[fn_SyspartGetChildren]

        //	(@root as int) returns @Subs TABLE

        //	( ID int  not null,

        //			lvl int not null,

        //	unique	clustered(lvl,id)   ---用于筛选级别的索引

        //	)AS

        //	BEGIN

        //		declare @lvl as int;

        //	set @lvl=0;

        //	Insert into @Subs(id,lvl) select id,@lvl from dbo.tb_Sys_part where id=@root; --插入根结点

        //	while @@rowcount >0   --当存在上级员工

        //	begin

        //		set @lvl=@lvl+1;  --递增级别计数器

        //		insert into @subs(id,lvl)

        //			select c.id,@lvl

        //			from @Subs as p			--p=父级

        //		join dbo.tb_Sys_part as c			--c=子级

        //		on p.lvl=@lvl -1      --从上一级筛选父亲

        //			and c.I_ParentID=p.id;

        //	end

        //	RETURN;

        //	END


        /// <summary>
        /// 通过ID来删除对象，加强版，包括其子树,注意：需要使用对应的函数fn_XXXGetChildren
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DelTree(int id)
        {


            IList<TbSysPagePower> items = GetByPage("ID in (select id from [fn_SyspartGetChildren](" + id.ToString() + ") ", 0, int.MaxValue);


            return Del(items);

        }


    }
}
